#!/usr/local/python3/bin/python3
import sys
import codecs
import os

import pymysql
import configparser
import json
import zipfile

from MmsLogging import get_logger

def zip_file(file_name):
    zip_file_name = "%s.zip" %(file_name)
    with zipfile.ZipFile(zip_file_name, 'w') as z:
        z.write(file_name)
    
    return zip_file_name

    
def get_ini_section_items(mms_ini_name, section_name):
    conf = configparser.ConfigParser()
    conf.read_file(codecs.open(mms_ini_name, "r", encoding="utf-8-sig"))
    #conf.read(mms_ini_name)
    cfg_ini = {}
    for item in conf.items(section_name):
        cfg_ini[item[0]] = item[1].split('#')[0].rstrip()
    return cfg_ini
    

def get_global_db_connect(host_ini, mandy_logger):
    try:
        db_connect = pymysql.connect(host=host_ini['db_host'], user=host_ini['db_user'], passwd=host_ini['db_pwd'], db=host_ini['db_name'])
        #db_connect = pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='monitor', charset='utf8')
        mandy_logger.info("connect to db:%s,%s success!" % (host_ini['db_host'], host_ini['db_name']) )
    except Exception as err:
        mandy_logger.error("Error %s unable to connect mysql server. please check mms.ini!" % (err))
        return (1,None)
    
    return (0,db_connect)

def get_global_param(mydb, mandy_logger):
    str_sql = ""
    mycursor = mydb.cursor()
    global_list = []
    try:
        str_sql = "select param_name, param_value from monitor_global where param_name != 'last_cfg_time' "
        count = mycursor.execute(str_sql)
        global_list = mycursor.fetchall()
    except:
        mandy_logger.error("except: mycursor.execute(str_sql):%s line:%d"% (str_sql, sys._getframe().f_lineno))
        #quit()
    finally:
        mycursor.close()
    
    global_dict = {}
    for param in global_list:
        global_dict[param[0]] = param[1]
        
    return global_dict
    

def update_realtime_instance_pid(mydb, mandy_logger, job_status_dict):
    if 'job_id' not in job_status_dict.keys():
        mandy_logger.error("update_job_status, job_id is require!")
        return
    if 'realtime_instance_pid' not in job_status_dict.keys():
        mandy_logger.error("update_job_status, realtime_instance_pid is require!")
        return
        
    update_job_str_sql = "update monitor_job set `realtime_instance_pid` = %d where id = %d" %(job_status_dict['realtime_instance_pid'], job_status_dict['job_id'])
         
    mycursor = mydb.cursor()
    count = 0
    try:
        count = mycursor.execute(update_job_str_sql)
        mandy_logger.debug("mycursor.execute(update_job_str_sql):%s line:%d"% (update_job_str_sql, sys._getframe().f_lineno))
        mydb.commit()
    except:
        mandy_logger.error("except: mycursor.execute(update_job_str_sql):%s line:%d"% (update_job_str_sql, sys._getframe().f_lineno))
    finally:
        mycursor.close()
        
    if (count == 1):
        return 0
    
    return 1

        
# 增加状态检查, modify_time 需要一致, 防止取得数据后, 后台状态又有变更
def update_task_process(mydb, mandy_logger, task_process_dict):
    # task_id, status, set_start_time, set_stop_time
    # if 'status' not in task_process_dict.keys():
    #     mandy_logger.error("update_task_process, status is require!")
    #     return 1
    modify_time_str = ""
    if 'modify_time' in task_process_dict.keys():
        modify_time_str = "and modify_time = STR_TO_DATE('%s', '%%Y-%%m-%%d %%T')" % (task_process_dict['modify_time'])
    if 'task_id' not in task_process_dict.keys():
        mandy_logger.error("update_task_process, task_id is require!")
        return 1
    
    #str_sql = "update monitor_task set `status` = '%s'" %(task_process_dict['status'])
    
    str_sql = "update monitor_task set id = id"
    
    if 'status' in task_process_dict.keys():
        str_sql += ", `status` = '%s' " %(task_process_dict['status'])
    if 'start_time' in task_process_dict.keys():
        str_sql += ", `start_time` = now() "
    if 'stop_time' in task_process_dict.keys():
        str_sql += ", `stop_time` = now() "
    if 'log_file_name' in task_process_dict.keys():
        str_sql += ", `log_file_name` = '%s' " %(task_process_dict['log_file_name'])
    if 'instance_pid' in task_process_dict.keys():
        str_sql += ", `instance_pid` = '%d'" % (task_process_dict['instance_pid'])
    if 'remark' in task_process_dict.keys(): 
        str_sql += ", `remark` = concat_ws(';', remark, DATE_FORMAT(now(), '%%Y-%%m-%%d %%T'),' %s')" % (task_process_dict['remark'])
    if 'return_code' in task_process_dict.keys():
        str_sql += ", `return_code` = '%d' " %(task_process_dict['return_code'])
    if 'conflict_flag' in task_process_dict.keys():
        str_sql += ", `conflict_flag` = '%s' " %(task_process_dict['conflict_flag'])
        
    str_sql +=" where `id` = %d %s" % (task_process_dict['task_id'], modify_time_str)
    mycursor = mydb.cursor()
    count = 0
    try:
        count = mycursor.execute(str_sql)
        mandy_logger.debug("mycursor.execute(str_sql):%s line:%d"% (str_sql, sys._getframe().f_lineno))
        mydb.commit()
    except:
        #quit()
        mandy_logger.error("except: mycursor.execute(str_sql):%s line:%d"% (str_sql, sys._getframe().f_lineno))
    finally:
        mycursor.close()
        
    if (count == 1):
        return 0
    
    return 1

        
def mycursor_execute_select_all(mydb, mandy_logger, str_sql):
    mycursor = mydb.cursor()
    data_set = []
    count = 0
    try:
        count = mycursor.execute(str_sql)
        #mandy_logger.debug("mycursor.execute(str_sql):%s line:%d"% (str_sql, sys._getframe().f_lineno))
        data_set = mycursor.fetchall()
    except:
        mandy_logger.error("except: mycursor.execute(str_sql):%s line:%d"% (str_sql, sys._getframe().f_lineno))
        #quit()
    finally:
        mycursor.close()
        
    return (count, data_set)
    
def mycursor_execute_select_one(mydb, mandy_logger, str_sql):
    mycursor = mydb.cursor()
    data_set = []
    try:
        count = mycursor.execute(str_sql)
        #mandy_logger.debug("mycursor.execute(str_sql):%s line:%d"% (str_sql, sys._getframe().f_lineno))
        data_set = mycursor.fetchone()
    except:
        mandy_logger.error("except: mycursor.execute(str_sql):%s line:%d"% (str_sql, sys._getframe().f_lineno))
        #quit()
    finally:
        mycursor.close()
    
    if count == 0:
        return (0)
    else:
        return (count, data_set[0])
        
def get_fields_dict(mydb, table_schema, table_name):
    mycursor = mydb.cursor()
    str_sql = "select lower(COLUMN_NAME) as COLUMN_NAME, DATA_TYPE from information_schema.`COLUMNS` where lower(TABLE_NAME) = lower('%s') and lower(table_schema) = lower('%s') order by ORDINAL_POSITION" %(table_name, table_schema)
    
    (count, field_list) = mycursor_execute_select_all(mydb, str_sql)
    fields_dict = {}
    for param in field_list:
        fields_dict[param[0]] = param[1]
    
    return fields_dict
    
def delete_data(mydb, mandy_logger, table_schema, table_name, table_data):
    fields_dict = get_fields_dict(mydb, table_schema, table_name)
    str_sql = "delete from %s.%s where id in("%(table_schema, table_name)

    row_list = []
    has_delete_data = 0
    for row in table_data:
        if row["tong_op"] != "delete":
            continue
        has_delete_data = 1
        row_list.append(str(row["id"]))
    str_sql += ",".join(row_list) + ")"
    
    if has_delete_data == 0:
        return 0
        
    mycursor = mydb.cursor()
    try:
        count = mycursor.execute(str_sql)
        mydb.commit()
    except:
        mandy_logger.error("delete_data %s:" %(str_sql))
        return -1
    finally:
        mycursor.close()
        
    mandy_logger.info("delete from %s.%s success, record count:%d"% (table_schema, table_name, count))
    return 0
    
def insert_data(mydb, mandy_logger, table_schema, table_name, table_data):
    fields_dict = get_fields_dict(mydb, table_schema, table_name)
    str_all_row = "insert into %s.%s("%(table_schema, table_name)
    for field_name in fields_dict.keys():
        str_all_row += "%s," %(field_name)
    str_all_row = str_all_row.rstrip(",")
    str_all_row += ") values "
    has_insert_data = 0
    row_list = []
    for row in table_data:
        if row["tong_op"] != "insert":
            continue
        has_insert_data = 1
        str_row_list = []
        for field_name in fields_dict.keys():
            if field_name == 'email_content':
                mandy_logger.debug("fields_dict[field_name]: %s" %(fields_dict[field_name]))
                
            if row[field_name] == None:
                str_row_list.append("null")
            elif fields_dict[field_name] == 'date':
                str_row_list.append("STR_TO_DATE('%s','%%Y%%m%%d')" % (row[field_name]))
            elif fields_dict[field_name] in ['datetime', 'timestamp']:
                str_row_list.append("STR_TO_DATE('%s','%%Y%%m%%d%%H%%i%%s')" % (row[field_name]))
            elif fields_dict[field_name] in ['varchar', 'char', 'text', 'longtext', 'mediumtext']:
                str_row_list.append("'%s'" %(row[field_name]))
            #elif fields_dict[field_name] in ['int', 'bigint', 'tinyint']:
            else:
                str_row_list.append(str(row[field_name]))
        str_row = "(" + ",".join(str_row_list) + ")"
        row_list.append(str_row)
        
    str_all_row += ",".join(row_list)
    
    mandy_logger.debug("str_all_row.rstrip: %s" %(str_all_row))
    
    if has_insert_data == 0:
        return 0
    mycursor = mydb.cursor()
    try:
        count = mycursor.execute(str_all_row)
        mydb.commit()
    except:
        mandy_logger.error("error!dksdfjskdff")
        return -1
    finally:
        mycursor.close()
        
    mandy_logger.info("insert into %s.%s success, record count:%d"% (table_schema, table_name, count))
    return 0
    
def update_data(mydb, mandy_logger, table_schema, table_name, table_data):
    fields_dict = get_fields_dict(mydb, table_schema, table_name)
    str_all_row_head = "update %s.%s set "%(table_schema, table_name)
    
    has_update_data = 0
    row_list = []
    for row in table_data:
        if row["tong_op"] != "update":
            continue
        has_update_data = 1
        str_row_list = []
        for field_name in fields_dict.keys():
            if field_name.strip().lower() == "id":
                continue
                
            str_field_value = "%s=" %(field_name)
        
            if row[field_name] == None:
                str_field_value +="null"
            elif fields_dict[field_name] == 'date':
                str_field_value +="STR_TO_DATE('%s','%%Y%%m%%d')" % (row[field_name])
            elif fields_dict[field_name] in ['datetime', 'timestamp']:
                str_field_value +="STR_TO_DATE('%s','%%Y%%m%%d%%H%%i%%s')" % (row[field_name])
            elif fields_dict[field_name] in ['varchar', 'char', 'text', 'longtext', 'mediumtext']:
                str_field_value +="'%s'" %(row[field_name].replace("'",r"\'"))
            #elif fields_dict[field_name] in ['int', 'bigint', 'tinyint']:
            else:
                str_field_value +=str(row[field_name])
                
            str_row_list.append(str_field_value)
            
        
        row_list.append(str_all_row_head + ",".join(str_row_list) + " where id=%d"%(row["id"]))
        
    # str_all_row= ";".join(row_list)
    
    if has_update_data == 0:
        return 0
    mycursor = mydb.cursor()
    try:
        for each_row in row_list:
            mycursor.execute(each_row)
        mydb.commit()
    except:
        mandy_logger.error("update %s.%s sql_str:%s error:%d"% (table_schema, table_name, each_row, sys._getframe().f_lineno))
        return -1
    finally:
        mycursor.close()
        
    mandy_logger.info("update %s.%s success, record count:%d"% (table_schema, table_name, len(row_list)))
    
    return 0
    
# table_name task_tong
def load_table_data(mydb, mandy_logger, table_schema, table_name, table_data):
    mandy_logger.debug("load_table_data:%d"% (sys._getframe().f_lineno))
    if len(table_data) == 0:
        return 0
    
    mandy_logger.debug("load_table_data:%d"% (sys._getframe().f_lineno))
    ret = insert_data(mydb, table_schema, table_name, table_data)
    if ret < 0:
        return ret
    #ret 等于新增记录数
    
    mandy_logger.debug("load_table_data:%d"% (sys._getframe().f_lineno))
    ret = update_data(mydb, table_schema, table_name, table_data)
    if ret < 0:
        return ret
    #ret 等于更新记录数
        
    mandy_logger.debug("load_table_data:%d"% (sys._getframe().f_lineno))
    ret = delete_data(mydb, table_schema, table_name, table_data)
    if ret < 0:
        return ret
        
    mandy_logger.debug("load_table_data:%d"% (sys._getframe().f_lineno))
    #ret 等于删除记录数
    return 0

    
'''
/root/sbin/data/center/hangzhou/syn_2/syn_1_hangzhou_20190906041006.json
'''
def load_db_data(mydb, table_schema, json_file_name):
    db_data = {}
    with open(json_file_name, "r") as of:
        db_data = json.load(of)
        
    for table_name in db_data.keys():
        fields_dict = get_fields_dict(mydb, table_schema, table_name)
        table_data = db_data[table_name]
        real_table = table_name
        if table_name.endswith("_tong"):
            mylen = len(table_name) - 5
            real_table = table_name[0:mylen]
            
        load_table_data(mydb, table_schema, real_table, table_data)
    
    
def get_tables(mydb, table_schema):
    str_sql = "select `table_name` from information_schema.`TABLES` where lower(TABLE_SCHEMA) = lower('%s')" %(table_schema)
    (count, data_set) = mycursor_execute_select_all(mydb, str_sql)
    return data_set
    

def get_now(mydb):
    str_sql = "select DATE_FORMAT(now(),'%Y%m%d%H%i%s')" 
    (count, data) = mycursor_execute_select_one(mydb, str_sql)
    return data
    

def tuple_to_dicts(fields_dict, data_set):
    record_list = []
    for record in data_set:
        idx = 0
        record_dict = {}
        for field_name in fields_dict.keys():
            record_dict[field_name] = record[idx]
            idx +=1
        record_list.append(record_dict)
    return record_list

#'''

#'''
# ************************************************************************
# main()
if __name__ == "__main__":
    print (os.getcwd())#获得当前目录
    print (os.path.abspath('.'))#获得当前工作目录
    print (os.path.abspath('..'))#获得当前工作目录的父目录
    print (os.path.abspath(os.curdir))#获得当前工作目录
    
    #host_ini = get_ini_section_items(os.path.abspath('./config') + "/mms.ini", "xgrather_server")
    host_ini = get_ini_section_items("/home/mandy/monitor/config/mms.ini", "xgrather_server")
    
    ret, mydb = get_global_db_connect(host_ini)
    print("ret", ret)
    #load_db_data(mydb, host_ini["db_name"], "/root/sbin/data/center/hangzhou/syn_1/syn_1_hangzhou_20190911085828.json")
    
    